from lxml import etree

import requests
import json

all_results = {}

def remove_province_city(s: str) -> str:
    for ch in ['省', '市']:
        s = s.replace(ch, '')
    return s

def parse_city_data(js_text):
    json_str = js_text.strip()
    json_str = json_str.replace("var city_data = ", "")
    if json_str.endswith(";"):
        json_str = json_str[:-1]

    city_data = json.loads(json_str)

    province_sql_list = []
    city_sql_list = []
    district_sql_list = []

    province_id_counter = 1
    city_id_counter = 1
    district_id_counter = 1

    for province_name, cities in city_data.items():
        province_id = f"P{province_id_counter:04d}"
        province_id_counter += 1

        # 省表插入语句
        province_sql_list.append(
            f"INSERT INTO province (id, name_cn) VALUES ('{province_id}', '{province_name}');"
        )

        for city_name, districts in cities.items():
            city_id = f"C{city_id_counter:04d}"
            city_id_counter += 1

            # 拼接省市名作为key，获取经纬度
            city_key = f"{province_name}{city_name}"
            city_key = remove_province_city(city_key)
            print(city_key)
            if city_key in all_results:
                city_longitude = all_results[city_key]['longitude']
                city_latitude = all_results[city_key]['latitude']
            else:
                city_longitude = "NULL"
                city_latitude = "NULL"

            city_sql_list.append(
                f"INSERT INTO city (id, name_cn, province_id, longitude, latitude) VALUES "
                f"('{city_id}', '{city_name}', '{province_id}', {city_longitude}, {city_latitude});"
            )

            for district_name, areas in districts.items():
                district_id = f"D{district_id_counter:04d}"
                district_id_counter += 1

                district_sql_list.append(
                    f"INSERT INTO district (id, name_cn, city_id) VALUES "
                    f"('{district_id}', '{district_name}', '{city_id}');"
                )

    return province_sql_list, city_sql_list, district_sql_list




def get_city_data():
    url = "https://j.i8tq.com/weather2020/search/city.js"
    response = requests.get(url)
    response.encoding = 'utf-8'

    city_sqls, district_sqls, area_sqls = parse_city_data(response.text)

    with open("insert_city_data.sql", "w", encoding="utf-8") as f:
        f.write("-- INSERT CITY\n")
        f.write("\n".join(city_sqls) + "\n\n")
        f.write("-- INSERT DISTRICT\n")
        f.write("\n".join(district_sqls) + "\n\n")
        f.write("-- INSERT AREA_INFO\n")
        f.write("\n".join(area_sqls))
    print("SQL 插入语句已保存至 insert_city_data.sql")



def get_lnglat_from_jsons(keyword: str):
    url = "http://www.jsons.cn/lngcode/"

    data = {
        "keyword": keyword,
        "txtflag": "0"
    }

    headers = {
        "User-Agent": "Mozilla/5.0",
        "Referer": "http://www.jsons.cn/lngcode/"
    }

    response = requests.post(url, data=data, headers=headers, timeout=10)
    response.encoding = 'utf-8'
    html = etree.HTML(response.text)

    # print(etree.tostring(html, pretty_print=True, encoding='unicode'))

    rows = html.xpath('//table[contains(@class, "table")]/tbody/tr')

    results = {}
    for row in rows:
        tds = row.xpath('./td')
        if len(tds) >= 3:
            province_city = tds[0].text.strip()
            region = tds[1].text.strip()
            key = remove_province_city(province_city)
            print(key)

            lnglat = tds[2].text.strip().replace('，', ',')  # 替换中文逗号

            if ',' in lnglat:
                lng, lat = map(str.strip, lnglat.split(','))
                results[key] = {
                    'longitude': float(lng),
                    'latitude': float(lat)
                }
            else:
                print(f"⚠️ 无效经纬度: '{lnglat}' 来自城市: {province_city} - {region}")

    return results

def get_city_list():
    url = "https://j.i8tq.com/weather2020/search/city.js"
    response = requests.get(url)
    response.encoding = 'utf-8'
    js_text = response.text
    # 去掉 `var city_data =` 和最后的分号，提取 JSON 字符串
    json_str = js_text.strip()
    json_str = json_str.replace("var city_data = ", "")
    if json_str.endswith(";"):
        json_str = json_str[:-1]

    # 转为 Python 字典
    city_data = json.loads(json_str)

    province_list = list(city_data.keys())
    return province_list

if __name__ == '__main__':
    citys = get_city_list()

    for city in citys:
        result = get_lnglat_from_jsons(city)
        if isinstance(result, dict):
            all_results.update(result)  # 合并字典

    get_city_data()





